package com.app.business.service;

import com.app.business.en.Status;
import com.app.business.model.*;
import com.app.business.utils.UserPermissionUtil;
import com.app.system.jpa.JpaBaseDao;
import com.app.system.jpa.PageBean;
import com.app.system.jpa.Wrapper;
import com.app.system.utils.CollectionUtils;
import com.app.system.utils.ParamUtils;
import com.app.system.utils.dataType.StringUtils;
import com.google.common.collect.Maps;
import com.google.common.collect.Sets;
import org.apache.commons.beanutils.ConvertUtils;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.persistence.Query;
import javax.servlet.http.HttpServletRequest;
import java.util.*;

/**
 * 统计查询专用
 * Created by ZYK on 2019/2/22.
 */
@SuppressWarnings(value = "ALL")
@Service
public class StatisticsService extends JpaBaseDao<Contract, Integer> {
    @Autowired
    private HouseService houseService;
    @Autowired
    private CarLotService carLotService;
    @Autowired
    private StorageRoomService storageRoomService;
    @Autowired
    private ShopHouseService shopHouseService;
    @Autowired
    private AzProjectService azProjectService;
    @Autowired
    private ZsProjectService zsProjectService;

    /**
     * 所有征收项目得到征收信息统计
     *
     * @param azProjectId
     * @return
     */
    public Map<String, Map> getContractSumByZsProjectMap() {
        List<Integer> zsProjectIdList = UserPermissionUtil.getZsProjectIdListByUser();
        StringBuffer sb = new StringBuffer("");

        if (CollectionUtils.isNotEmpty(zsProjectIdList)) {
            for (Integer zsProjectId : zsProjectIdList) {
                zsProjectId = Integer.valueOf(zsProjectId);

                sb.append("'" + zsProjectId + "',");
            }
            if (sb.length() > 0) {
                sb = new StringBuffer(sb.substring(0, sb.length() - 1));
            }
        }

        StringBuffer sql = new StringBuffer("select c.zs_project_id as zsProjectId,zsp.project_name as zsProjectName ,ifnull(COUNT(c.id),0) as contractTotal,");
        sql.append("SUM(IFNULL(c.property_change_area,0)) + SUM(IFNULL(c.care_buy_area1,0)) + SUM(IFNULL(c.care_buy_area2,0))+ SUM(IFNULL(c.care_buy_area3,0))+ SUM(IFNULL(c.care_buy_area4,0))+ SUM(IFNULL(c.care_buy_area5,0))+ SUM(IFNULL(c.care_buy_area6,0))+ SUM(IFNULL(c.care_buy_area7,0))+ SUM(IFNULL(c.care_buy_area8,0))+ SUM(IFNULL(c.adjust_area,0)) AS limitHouseAreaTotal,");

        sql.append("SUM(IFNULL(c.normal_house_num,0))+SUM(IFNULL(c.small_house_num,0)) AS houseNum,");
        sql.append("SUM(IFNULL(c.storage_room_num,0)) AS storageRoomNum,");
        sql.append("SUM(IFNULL(c.car_lot_num,0)) AS carLotNum,");

        sql.append("SUM(IFNULL(c.normal_house_area,0)) +SUM(IFNULL(c.small_house_area,0)) AS houseArea,");
        sql.append("SUM(IFNULL(c.storage_room_area,0)) AS storageRoomArea,");
        sql.append("SUM(IFNULL(c.car_lot_area,0)) AS carLotArea,");

        sql.append("SUM(IFNULL(c.house_money,0)) AS houseMoney,");
        sql.append("SUM(IFNULL(c.storage_room_money,0)) AS storageRoomMoney,");
        sql.append("SUM(IFNULL(c.car_lot_money,0)) AS carLotMoney,");
        sql.append("SUM(IFNULL(c.house_one_money,0)) AS houseOneMoney,");
        sql.append("SUM(IFNULL(c.wx_money_total,0)) AS wxMoneyTotal,");

        sql.append("SUM(case when c.choose_house_date is not null then IFNULL(c.final_money,0) else 0 end) AS finalMoney ");

        sql.append(" from contract c inner join zs_project zsp on c.zs_project_id = zsp.id ");
        sql.append(" where c.zs_project_id in (" + sb + ")");
        sql.append(" group by c.zs_project_id order by c.zs_project_id");
        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map> contractNumMap = Maps.newHashMap();
        if (CollectionUtils.isNotEmpty(mapList)) {
            for (Map map : mapList) {
                String zsProjectId = map.get("zsProjectId").toString();
                contractNumMap.put(zsProjectId, map);
            }
        }
        return contractNumMap;
    }


    /**
     * 房源选择情况
     * Map<azProjectId,Map<BuildNum,Map>>
     *
     * @return
     */
    public Map<String, Map<String, Map>> getHouseCountMapByAzProjectId() {
        StringBuffer sql = new StringBuffer("select h.az_project_id,h.build_num,count(h.id) as allCount,");
        sql.append("SUM(IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) AS allCountArea, ");
        sql.append("sum(case when h.house_status = 2 then 1 else 0 end) as choose,");
        sql.append("SUM(CASE WHEN h.house_status = 2 THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS chooseArea, ");
        sql.append("sum(case when h.house_status = 1 then 1 else 0 end) as unChoose,");
        sql.append("SUM(CASE WHEN h.house_status = 1 THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS unChooseArea ");
        sql.append(" from house h group by h.az_project_id,h.build_num order by h.az_Project_id,h.build_num");
        List<Map> mapList = this.entityManager.createNativeQuery(sql.toString())
                .unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map<String, Map>> resultMap = Maps.newLinkedHashMap();
        if (CollectionUtils.isNotEmpty(mapList)) {
            Map<String, Map> itemMap = Maps.newLinkedHashMap();
            for (Map map : mapList) {
                String az_project_id = String.valueOf(map.get("az_project_id"));
                String buildNum = String.valueOf(map.get("build_num"));

                itemMap = resultMap.get(az_project_id) == null ? new LinkedHashMap<>() : resultMap.get(az_project_id);
                itemMap.put(buildNum, map);
                resultMap.put(az_project_id, itemMap);
            }
        }
        return resultMap;
    }


    /**
     * 车位选择情况
     * Map<azProjectId,Map>
     *
     * @return
     */
    public Map<String, Map> getCarLotCountMapByAzProjectId() {
        StringBuffer sql = new StringBuffer("select c.az_project_id,count(c.id) as allCount,");//所有数量
        sql.append("sum(ifnull(c.build_area,0)) as allCountArea,");//所有面积
        sql.append("sum(case when c.house_status =2 then 1 else 0 end) as choose,");//已选数量
        sql.append("sum(case when c.house_status =2 and c.car_lot_type in (1,7,15,2,3,16,9,10,11,12,13,17,18,23) then 1 else 0 end) as ptChoose,");//已选普通数量
        sql.append("sum(case when c.house_status =2 and c.car_lot_type in (4,8,5,6,14,19,20,21,22) then 1 else 0 end) as rfChoose,");//已选人防数量
        sql.append("sum(case when c.house_status =2 then ifnull(c.price,0) else 0 end) as chooseMoney, ");//已选申购金额
        sql.append("sum(case when c.house_status =2 then ifnull(c.build_area,0) else 0 end) as chooseArea,");//已选面积
        sql.append("sum(case when c.house_status =2 and c.finance_Settle_Status = 2  then 1 else 0 end) as financeSettleStatusCount,");//已选已结清数量
        sql.append("sum(case when c.house_status =2 and c.finance_Settle_Status = 2 and c.car_lot_type in (1,7,15,2,3,16,9,10,11,12,13,17,18,23) then 1 else 0 end) as ptFinanceSettleStatusCount,");//已选已结清普通数量
        sql.append("sum(case when c.house_status =2 and c.finance_Settle_Status = 2 and c.car_lot_type in (4,8,5,6,14,19,20,21,22) then 1 else 0 end) as rfFinanceSettleStatusCount,");//已选已结清人防数量
        sql.append("sum(case when c.house_status  =2 and c.finance_Settle_Status = 2  then ifnull(c.price,0) else 0 end) as financeSettleStatusMoney, ");//已选已结清金额

        sql.append("sum(case when c.house_status = 1 then 1 else 0 end) as unChoose,");//未选数量
        sql.append("sum(case when c.house_status = 1 then ifnull(c.build_area,0) else 0 end) as unChooseArea, ");//未选面积
        sql.append("sum(case when c.car_lot_type in (1,7,15,2,3,16,9,10,11,12,13,17,18,23) then 1 else 0 end) as normalType,");//普通车位数量
        sql.append("sum(case when c.car_lot_type in (1,7,15,2,3,16,9,10,11,12,13,17,18,23) then ifnull(c.build_area,0) else 0 end) as normalTypeArea,");//普通车位面积
        sql.append("sum(case when c.car_lot_type in (1,7,15,2,3,16,9,10,11,12,13,17,18,23) and c.house_status != 1 then 1 else 0 end) as normalTypeChoose,");//普通车位已选数量
        sql.append("sum(case when c.car_lot_type in (1,7,15,2,3,16,9,10,11,12,13,17,18,23) and c.house_status != 1 then ifnull(c.build_area,0) else 0 end) as normalTypeChooseArea,");//普通车位已选面积
        sql.append("sum(case when c.car_lot_type in (1,7,15,2,3,16,9,10,11,12,13,17,18,23) and c.house_status = 1 then 1 else 0 end) as normalTypeUnChoose,");//普通车位未选数量
        sql.append("sum(case when c.car_lot_type in (1,7,15,2,3,16,9,10,11,12,13,17,18,23) and c.house_status = 1 then ifnull(c.build_area,0) else 0 end) as normalTypeUnChooseArea,");//普通车位未选面积
        sql.append("sum(case when c.car_lot_type in (4,8,5,6,14,19,20,21,22) then 1 else 0 end) as rfType,");//人防车位数量
        sql.append("sum(case when c.car_lot_type in (4,8,5,6,14,19,20,21,22) then ifnull(c.build_area,0) else 0 end) as rfTypeArea ,");//人防车位面积
        sql.append("sum(case when c.car_lot_type in (4,8,5,6,14,19,20,21,22) and c.house_status != 1 then 1 else 0 end) as rfTypeChoose,");//人防车位已选数量
        sql.append("sum(case when c.car_lot_type in (4,8,5,6,14,19,20,21,22) and c.house_status != 1 then ifnull(c.build_area,0) else 0 end) as rfTypeChooseArea,");//人防车位已选面积
        sql.append("sum(case when c.car_lot_type in (4,8,5,6,14,19,20,21,22) and c.house_status = 1 then 1 else 0 end) as rfTypeUnChoose,");//人防车位未选数量
        sql.append("sum(case when c.car_lot_type in (4,8,5,6,14,19,20,21,22) and c.house_status = 1 then ifnull(c.build_area,0) else 0 end) as rfTypeUnChooseArea ");//人防车位未选面积
        sql.append(" from car_lot c group by c.az_project_id ");
        List<Map> mapList = this.entityManager.createNativeQuery(sql.toString())
                .unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map> resultMap = Maps.newLinkedHashMap();
        if (CollectionUtils.isNotEmpty(mapList)) {
            for (Map map : mapList) {
                String az_project_id = String.valueOf(map.get("az_project_id"));
                resultMap.put(az_project_id, map);
            }
        }
        return resultMap;
    }

    /**
     * 店铺选择情况
     * Map<azProjectId,Map>
     *
     * @return
     */
    public Map<String, Map> getShopHouseCountMapByAzProjectId() {
        StringBuffer sql = new StringBuffer("select s.az_project_id,count(s.id) as allCount,");
        sql.append("sum(ifnull(s.build_area,0)) as allCountArea,");
        sql.append("sum(case when s.house_status = 2 then 1 else 0 end) as choose,");
        sql.append("sum(case when s.house_status = 2 then ifnull(s.build_area,0)  else 0 end) as chooseArea,");
        sql.append("sum(case when s.house_status = 1 then 1 else 0 end) as unChoose,");
        sql.append("sum(case when s.house_status = 1 then ifnull(s.build_area,0)  else 0 end) as unChooseArea ");
        sql.append(" from shop_house s group by s.az_project_id ");
        List<Map> mapList = this.entityManager.createNativeQuery(sql.toString())
                .unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map> resultMap = Maps.newLinkedHashMap();
        if (CollectionUtils.isNotEmpty(mapList)) {
            for (Map map : mapList) {
                Integer az_project_id = Integer.valueOf(String.valueOf(map.get("az_project_id")));
                resultMap.put(az_project_id.toString(), map);
            }
        }
        return resultMap;
    }


    /**
     * 储物间选择情况
     * Map<azProjectId,Map>
     *
     * @return
     */
    public Map<String, Map> getStorageRoomCountMapByAzProjectId() {
        StringBuffer sql = new StringBuffer("select s.az_project_id,count(s.id) as allCount,");
        sql.append("sum(ifnull(s.build_area,0)) as allCountArea,");
        sql.append("sum(case when s.house_status = 2 then 1 else 0 end) as choose,");
        sql.append("sum(case when s.house_status = 2 then ifnull(s.build_area,0)  else 0 end) as chooseArea,");
        sql.append("sum(case when s.house_status = 1 then 1 else 0 end) as unChoose,");
        sql.append("sum(case when s.house_status = 1 then ifnull(s.build_area,0)  else 0 end) as unChooseArea ");
        sql.append(" from storage_room s group by s.az_project_id ");
        List<Map> mapList = this.entityManager.createNativeQuery(sql.toString())
                .unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map> resultMap = Maps.newLinkedHashMap();
        if (CollectionUtils.isNotEmpty(mapList)) {
            for (Map map : mapList) {
                Integer az_project_id = Integer.valueOf(String.valueOf(map.get("az_project_id")));
                resultMap.put(az_project_id.toString(), map);
            }
        }
        return resultMap;
    }

    /**
     * 选房情况汇总表
     * 根据安置项目得到已选该安置区协议的数量及可安置面积及财务结算金额
     *
     * @return
     */
    public Map<String, Map> getContractStatisticsByAzProjectId() {

        StringBuffer sql = new StringBuffer("SELECT n.azProjectId as azProjectId ,COUNT(c.id) as contractTotal, ");

        sql.append(" SUM(CASE WHEN c.is_Ji_Suan =1  THEN (IFNULL(c.property_change_area,0) + IFNULL(c.care_buy_area1,0) + ");
        sql.append(" IFNULL(c.care_buy_area2,0)+ IFNULL(c.care_buy_area3,0)+ IFNULL(c.care_buy_area4,0)+ IFNULL(c.care_buy_area5,0)+ ");
        sql.append(" IFNULL(c.care_buy_area6,0)+ IFNULL(c.care_buy_area7,0)+ IFNULL(c.care_buy_area8,0)+ IFNULL(c.adjust_area,0)) ELSE 0 END)+ ");
        sql.append(" SUM(CASE WHEN c.is_Ji_Suan IS NOT NULL AND c.is_Ji_Suan != 1   THEN IFNULL(c.choose_House_Area_Total,0) ELSE 0 END) AS limitHouseAreaTotal,  ");

        sql.append(" SUM(CASE WHEN c.final_money >= 0 THEN 1 ELSE 0 END) AS moreCount, ");
        sql.append(" SUM(CASE WHEN c.final_money >= 0 THEN IFNULL(c.final_money,0) ELSE 0 END) AS moreMoney, ");
        sql.append(" SUM(CASE WHEN c.final_money < 0 THEN 1 ELSE 0 END) AS lessCount, ");
        sql.append(" SUM(CASE WHEN c.final_money < 0 THEN IFNULL(c.final_money,0) ELSE 0 END) AS lessMoney, ");
        sql.append(" SUM(IFNULL(c.wx_Money_Total,0)) AS wxMoney, ");
        sql.append(" SUM(IFNULL(c.car_Lot_Money,0)) AS carLotMoney, ");
        sql.append(" SUM(IFNULL(c.final_money,0)) AS finalMoney ");
        sql.append("  FROM contract c,");
        sql.append(" (SELECT DISTINCT c.id AS contractId,h.az_project_id AS azProjectId FROM house h,contract c WHERE h.contract_id = c.id) n");
        sql.append(" WHERE c.id = n.contractId and c.choose_house_status =2");
        sql.append(" GROUP BY n.azProjectId");
        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map> contractNumMap = Maps.newHashMap();
        if (CollectionUtils.isNotEmpty(mapList)) {
            for (Map map : mapList) {
                String azProjectId = map.get("azProjectId").toString();
                contractNumMap.put(azProjectId, map);
            }
        }
        return contractNumMap;
    }


    /**
     * 选房情况汇总表
     * 根据安置项目得到已选该安置区房源的数量及面积
     *
     * @return
     */
    public Map<String, Map> getHouseStatisticsByAzProjectId() {

        StringBuffer sql = new StringBuffer("SELECT azp.id AS azProjectId,azp.project_name AS azProjectName,");
        //普通型
        sql.append(" SUM(CASE WHEN h.house_type = '01' THEN 1 ELSE 0 END) AS normalHouseNum,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS normalHouseArea,");
        //小户型
        sql.append(" SUM(CASE WHEN h.house_type = '02' THEN 1 ELSE 0 END) AS smallHouseNum,");
        sql.append(" SUM(CASE WHEN h.house_type = '02' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS smallHouseArea, ");
        //复式
        sql.append(" SUM(CASE WHEN h.house_type = '03' THEN 1 ELSE 0 END) AS fsHouseNum,");
        sql.append(" SUM(CASE WHEN h.house_type = '03' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS fsHouseArea, ");
        //小户型户型
        sql.append(" SUM(CASE WHEN h.house_hold = '0100' AND h.house_type = '02' THEN 1 ELSE 0 END) AS 0100HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_hold = '0100' AND h.house_type = '02' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0100HouseArea, ");
        //普通型户型
        sql.append(" SUM(CASE WHEN h.house_hold = '0102' AND h.house_type = '01' THEN 1 ELSE 0 END) AS 0102HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_hold = '0102' AND h.house_type = '01' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0102HouseArea, ");
        sql.append(" SUM(CASE WHEN h.house_hold = '0201' AND h.house_type = '01' THEN 1 ELSE 0 END) AS 0201HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_hold = '0201' AND h.house_type = '01' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0201HouseArea, ");
        sql.append(" SUM(CASE WHEN h.house_hold = '0202' AND h.house_type = '01' THEN 1 ELSE 0 END) AS 0202HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_hold = '0202' AND h.house_type = '01' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0202HouseArea, ");
        sql.append(" SUM(CASE WHEN h.house_hold = '0302' AND h.house_type = '01' THEN 1 ELSE 0 END) AS 0302HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_hold = '0302' AND h.house_type = '01' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0302HouseArea, ");
        sql.append(" SUM(CASE WHEN h.house_hold = '0402' AND h.house_type = '01' THEN 1 ELSE 0 END) AS 0402HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_hold = '0402' AND h.house_type = '01' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0402HouseArea, ");
        sql.append(" SUM(CASE WHEN h.house_hold = '0502' AND h.house_type = '01' THEN 1 ELSE 0 END) AS 0502HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_hold = '0502' AND h.house_type = '01' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0502HouseArea, ");

        sql.append(" IFNULL(COUNT(DISTINCT(h.id)),0) AS houseNum,");
        sql.append(" SUM(IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) AS houseArea ");
        sql.append(" FROM house h INNER JOIN az_project azp ON h.az_project_id = azp.id ");
        sql.append(" WHERE h.house_status =2 ");
        sql.append(" GROUP BY azp.id ORDER BY azp.id");

        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map> contractNumMap = Maps.newHashMap();
        if (CollectionUtils.isNotEmpty(mapList)) {
            for (Map map : mapList) {
                String azProjectId = map.get("azProjectId").toString();
                contractNumMap.put(azProjectId, map);
            }
        }
        return contractNumMap;
    }


    /**
     * 选房情况汇总表根据乡镇、征收项目
     * 根据安置项目得到已选该安置区的家庭协议的数量、面积、财务结算
     * 根据征收项目group
     *
     * @return
     */
    public Map<String, Map> getContractZsStatisticsForAll() {

        StringBuffer sql = new StringBuffer("SELECT c.zs_project_id AS zsProjectId, ");
        sql.append(" SUM(CASE WHEN c.choose_house_area_total > 0 THEN 1 ELSE 0 END) AS contractTotal, ");

        sql.append(" MIN(c.choose_house_date) AS zsProjectStartDate, ");

        sql.append(" SUM(CASE WHEN c.is_Ji_Suan =1  THEN (IFNULL(c.property_change_area,0) + IFNULL(c.care_buy_area1,0) + ");
        sql.append(" IFNULL(c.care_buy_area2,0)+ IFNULL(c.care_buy_area3,0)+ IFNULL(c.care_buy_area4,0)+ IFNULL(c.care_buy_area5,0)+ ");
        sql.append(" IFNULL(c.care_buy_area6,0)+ IFNULL(c.care_buy_area7,0)+ IFNULL(c.care_buy_area8,0)+ IFNULL(c.adjust_area,0)) ELSE 0 END) + ");
        sql.append(" SUM(CASE WHEN c.is_Ji_Suan IS NOT NULL AND c.is_Ji_Suan != 1   THEN IFNULL(c.choose_House_Area_Total,0) ELSE 0 END) AS limitHouseAreaTotal,  ");

        sql.append(" SUM(IFNULL(c.house_num,0)) AS houseNum, ");
        sql.append(" SUM(IFNULL(c.choose_house_area_total,0)) AS chooseHouseAreaTotal  ");
        sql.append(" FROM contract c INNER JOIN zs_project zsp ON c.zs_project_id = zsp.id ");
        sql.append(" WHERE c.choose_house_status =2 ");
        sql.append(" GROUP BY c.zs_project_id ");

        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map> contractNumMap = Maps.newHashMap();
        if (CollectionUtils.isNotEmpty(mapList)) {
            for (Map map : mapList) {
                String zsProjectId = map.get("zsProjectId").toString();
                contractNumMap.put(zsProjectId, map);
            }
        }
        return contractNumMap;
    }


    /**
     * 选房情况汇总表
     * 根据安置项目得到已选该安置区的家庭协议的数量、面积、财务结算
     * 根据征收项目group
     *
     * @return
     */
    public Map<String, Map> getContractZsStatisticsByAzProjectId(Integer azProjectId) {

        StringBuffer sql = new StringBuffer("SELECT n.azProjectId AS azProjectId,c.zs_project_id AS zsProjectId,COUNT(c.id) AS contractTotal, ");
        sql.append(" MIN(c.choose_house_date) AS zsProjectStartDate, ");

        sql.append(" SUM(CASE WHEN c.is_Ji_Suan =1  THEN (IFNULL(c.property_change_area,0) + IFNULL(c.care_buy_area1,0) + ");
        sql.append(" IFNULL(c.care_buy_area2,0)+ IFNULL(c.care_buy_area3,0)+ IFNULL(c.care_buy_area4,0)+ IFNULL(c.care_buy_area5,0)+ ");
        sql.append(" IFNULL(c.care_buy_area6,0)+ IFNULL(c.care_buy_area7,0)+ IFNULL(c.care_buy_area8,0)+ IFNULL(c.adjust_area,0)) ELSE 0 END) + ");
        sql.append(" SUM(CASE WHEN c.is_Ji_Suan IS NOT NULL AND c.is_Ji_Suan != 1   THEN IFNULL(c.choose_House_Area_Total,0) ELSE 0 END) AS limitHouseAreaTotal,  ");

        sql.append(" SUM(CASE WHEN c.final_money >= 0 THEN 1 ELSE 0 END) AS moreCount, ");
        sql.append(" SUM(CASE WHEN c.final_money >= 0 THEN IFNULL(c.final_money,0) ELSE 0 END) AS moreMoney, ");
        sql.append(" SUM(CASE WHEN c.final_money < 0 THEN 1 ELSE 0 END) AS lessCount, ");
        sql.append(" SUM(CASE WHEN c.final_money < 0 THEN IFNULL(c.final_money,0) ELSE 0 END) AS lessMoney,  ");
        sql.append(" SUM(IFNULL(c.wx_Money_Total,0)) AS wxMoney, ");
        sql.append(" SUM(IFNULL(c.car_Lot_Money,0)) AS carLotMoney, ");
        sql.append(" SUM(IFNULL(c.final_money,0)) AS finalMoney  ");
        sql.append(" FROM contract c INNER JOIN zs_project zsp ON c.zs_project_id = zsp.id, ");
        sql.append(" (SELECT DISTINCT c.id AS contractId,h.az_project_id AS azProjectId FROM house h,contract c WHERE h.contract_id = c.id) n ");
        sql.append(" WHERE c.id = n.contractId AND n.azProjectId=:azProjectId and c.choose_house_status =2 ");
        sql.append(" GROUP BY c.zs_project_id ");

        Query query = this.entityManager.createNativeQuery(sql.toString()).setParameter("azProjectId", azProjectId);
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map> contractNumMap = Maps.newHashMap();
        if (CollectionUtils.isNotEmpty(mapList)) {
            for (Map map : mapList) {
                String zsProjectId = map.get("zsProjectId").toString();
                contractNumMap.put(zsProjectId, map);
            }
        }
        return contractNumMap;
    }

    /**
     * 选房情况汇总表
     * 根据安置项目得到已选该安置区协议数量面积及财务结算金额
     * 根据征收项目group
     *
     * @return
     */
    public Map<String, Map> getHouseZsStatisticsByAzProjectId(Integer azProjectId) {

        StringBuffer sql = new StringBuffer("SELECT zsp.id AS zsProjectId,zsp.project_name AS zsProjectName,");
        sql.append(" zsp.start_year AS zsProjectDate,");
        //普通型
        sql.append(" SUM(CASE WHEN h.house_type = '01' THEN 1 ELSE 0 END) AS normalHouseNum,");
        sql.append(" SUM(CASE WHEN h.house_type = '01' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS normalHouseArea,");
        //小户型
        sql.append(" SUM(CASE WHEN h.house_type = '02' THEN 1 ELSE 0 END) AS smallHouseNum,");
        sql.append(" SUM(CASE WHEN h.house_type = '02' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS smallHouseArea, ");
        //复式
        sql.append(" SUM(CASE WHEN h.house_type = '03' THEN 1 ELSE 0 END) AS fsHouseNum,");
        sql.append(" SUM(CASE WHEN h.house_type = '03' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS fsHouseArea, ");
        //小户型户型
        sql.append(" SUM(CASE WHEN h.house_hold = '0100' AND h.house_type = '02' THEN 1 ELSE 0 END) AS 0100HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_hold = '0100' AND h.house_type = '02' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0100HouseArea, ");
        //普通型户型
        sql.append(" SUM(CASE WHEN h.house_hold = '0102' THEN 1 ELSE 0 END) AS 0102HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_hold = '0102' AND h.house_type = '01' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0102HouseArea, ");
        sql.append(" SUM(CASE WHEN h.house_hold = '0201' AND h.house_type = '01' THEN 1 ELSE 0 END) AS 0201HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_hold = '0201' AND h.house_type = '01' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0201HouseArea, ");
        sql.append(" SUM(CASE WHEN h.house_hold = '0202' AND h.house_type = '01' THEN 1 ELSE 0 END) AS 0202HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_hold = '0202' AND h.house_type = '01' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0202HouseArea, ");
        sql.append(" SUM(CASE WHEN h.house_hold = '0302' AND h.house_type = '01' THEN 1 ELSE 0 END) AS 0302HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_hold = '0302' AND h.house_type = '01' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0302HouseArea, ");
        sql.append(" SUM(CASE WHEN h.house_hold = '0402' AND h.house_type = '01' THEN 1 ELSE 0 END) AS 0402HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_hold = '0402' AND h.house_type = '01' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0402HouseArea, ");
        sql.append(" SUM(CASE WHEN h.house_hold = '0502' AND h.house_type = '01' THEN 1 ELSE 0 END) AS 0502HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_hold = '0502' AND h.house_type = '01' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0502HouseArea, ");
        //总数量、面积
        sql.append(" IFNULL(COUNT(DISTINCT(h.id)),0) AS houseNum,");
        sql.append(" SUM(IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) AS houseArea ");
        sql.append(" FROM house h INNER JOIN contract c ON h.contract_id = c.id INNER JOIN zs_project zsp ON c.zs_project_id = zsp.id ");
        sql.append(" WHERE h.house_status =2 AND c.choose_house_status =2 AND h.az_project_id =:azProjectId ");
        sql.append(" GROUP BY zsp.id ORDER BY zsp.start_year");

        Query query = this.entityManager.createNativeQuery(sql.toString()).setParameter("azProjectId", azProjectId);
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map> contractNumMap = Maps.newHashMap();
        if (CollectionUtils.isNotEmpty(mapList)) {
            for (Map map : mapList) {
                String zsProjectId = map.get("zsProjectId").toString();
                contractNumMap.put(zsProjectId, map);
            }
        }
        return contractNumMap;
    }

    public List<Integer> getZsProjectIdListByAzProjectId(Integer azProjectId) {
        StringBuffer hql = new StringBuffer("select DISTINCT(zsp.id) AS zsProjectId ");
        hql.append(" FROM house h INNER JOIN contract c ON h.contract_id = c.id INNER JOIN zs_project zsp ON c.zs_project_id = zsp.id ");
        hql.append(" WHERE h.house_status =2 AND c.choose_house_status =2 AND h.az_project_id =:azProjectId ");
        hql.append(" UNION ");
        hql.append(" SELECT DISTINCT(zsp.id) AS zsProjectId ");
        hql.append(" FROM car_lot cl INNER JOIN contract c ON cl.contract_id = c.id INNER JOIN zs_project zsp ON c.zs_project_id = zsp.id ");
        hql.append(" WHERE cl.house_status =2 AND c.choose_house_status =2 AND cl.az_project_id =:azProjectId");
        hql.append(" UNION ");
        hql.append(" SELECT DISTINCT(zsp.id) AS zsProjectId ");
        hql.append(" FROM storage_room sr INNER JOIN contract c ON sr.contract_id = c.id INNER JOIN zs_project zsp ON c.zs_project_id = zsp.id ");
        hql.append(" WHERE sr.house_status =2 AND c.choose_house_status =2 AND sr.az_project_id =:azProjectId");

        Map<String, Object> map = new LinkedHashMap<>();
        map.put("azProjectId", azProjectId);

        List<Integer> list = this.findSqlObject(hql.toString(), map);
        return list;
    }

    /**
     * 通过安置项目id获取所有在该项目下已选房的征收项目
     *
     * @param azProjectId
     * @return
     */
    public String findZsProjectIdSetByAzProjectId(Integer azProjectId) {
        List<Integer> list = this.getZsProjectIdListByAzProjectId(azProjectId);
        Set<String> resultSet = Sets.newHashSet();
        if (CollectionUtils.isNotEmpty(list)) {
            for (Integer zsProjectId : list) {
                resultSet.add(String.valueOf(zsProjectId));
            }
        }
        String resultString = String.join(",", resultSet);
        if (!resultString.isEmpty() & resultString.length() > 0) {
            resultString.substring(0, resultString.length() - 1);
        }
        return resultString;
    }

    /**
     * 选房情况汇总表
     * 根据安置项目得到已选该安置区储物间的数量及面积
     * 根据征收项目group
     *
     * @return
     */
    public Map<String, Map> getStorageRoomZsStatisticsByAzProjectId(Integer azProjectId) {

        StringBuffer sql = new StringBuffer("SELECT zsp.id AS zsProjectId,zsp.project_name AS zsProjectName ,IFNULL(COUNT(DISTINCT(s.id)),0) AS storageRoomTotal,");
        sql.append(" SUM(IFNULL(s.build_Area,0)) AS storageRoomArea ");
        sql.append(" FROM storage_Room s INNER JOIN contract c ON s.contract_id = c.id INNER JOIN zs_project zsp ON c.zs_project_id = zsp.id ");
        sql.append(" WHERE s.house_status =2 AND c.choose_house_status =2 AND s.az_project_id =:azProjectId ");
        sql.append(" GROUP BY zsp.id ");

        Query query = this.entityManager.createNativeQuery(sql.toString()).setParameter("azProjectId", azProjectId);
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map> storageRoomNumMap = Maps.newHashMap();
        if (CollectionUtils.isNotEmpty(mapList)) {
            for (Map map : mapList) {
                String zsProjectId = map.get("zsProjectId").toString();
                storageRoomNumMap.put(zsProjectId, map);
            }
        }
        return storageRoomNumMap;
    }

    /**
     * 选房情况汇总表
     * 根据安置项目得到已选该安置区店铺的数量及面积
     * 根据征收项目group
     *
     * @return
     */
    public Map<String, Map> getShopHouseZsStatisticsByAzProjectId(Integer azProjectId) {

        StringBuffer sql = new StringBuffer("SELECT zsp.id AS zsProjectId,zsp.project_name AS zsProjectName ,IFNULL(COUNT(DISTINCT(s.id)),0) AS shopHouseTotal,");
        sql.append(" SUM(IFNULL(s.build_Area,0)) AS shopHouseArea ");
        sql.append(" FROM shop_house s INNER JOIN contract c ON s.contract_id = c.id INNER JOIN zs_project zsp ON c.zs_project_id = zsp.id ");
        sql.append(" WHERE s.house_status =2 AND c.choose_house_status =2 AND s.az_project_id =:azProjectId ");
        sql.append(" GROUP BY zsp.id ");

        Query query = this.entityManager.createNativeQuery(sql.toString()).setParameter("azProjectId", azProjectId);
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map> storageRoomNumMap = Maps.newHashMap();
        if (CollectionUtils.isNotEmpty(mapList)) {
            for (Map map : mapList) {
                String zsProjectId = map.get("zsProjectId").toString();
                storageRoomNumMap.put(zsProjectId, map);
            }
        }
        return storageRoomNumMap;
    }

    /**
     * 选房情况汇总表
     * 根据安置项目得到已选该安置区房源的协议数及安置面积
     * 根据征收项目group
     *
     * @return
     */
    public Map<String, Map> getCarLotZsStatisticsByAzProjectId(Integer azProjectId) {

        StringBuffer sql = new StringBuffer("SELECT zsp.id AS zsProjectId,zsp.project_name AS zsProjectName ,IFNULL(COUNT(DISTINCT(cl.id)),0) AS chooseCount,");
        sql.append(" SUM(IFNULL(cl.build_Area,0)) AS carLotArea, ");
        sql.append(" SUM(IFNULL(cl.price,0)) AS choosePrice ,");

        sql.append(" sum(case when cl.house_status =2 and cl.car_lot_type in (1,7,15,2,3,16,9,10,11,12,13,17,18,23) then 1 else 0 end) as ptChoose,");//已选普通数量
        sql.append(" sum(case when cl.house_status =2 and cl.car_lot_type in (4,8,5,6,14,19,20,21,22) then 1 else 0 end) as rfChoose,");//已选人防数量

        sql.append(" sum(case when cl.house_status =2 and cl.finance_Settle_Status = 2 and cl.car_lot_type in (1,7,15,2,3,16,9,10,11,12,13,17,18,23) then 1 else 0 end) as ptFinanceSettleStatusCount,");//已选已结清普通数量
        sql.append(" sum(case when cl.house_status =2 and cl.finance_Settle_Status = 2 and cl.car_lot_type in (4,8,5,6,14,19,20,21,22) then 1 else 0 end) as rfFinanceSettleStatusCount,");//已选已结清人防数
        sql.append(" sum(case when  cl.finance_Settle_Status = 2  then 1 else 0 end) as financeSettleStatusCount,");//已选已结清数量
        sql.append(" sum(case when  cl.finance_Settle_Status = 2  then ifnull(cl.price,0) else 0 end) as financeSettleStatusMoney ");//已选已结

        sql.append(" FROM car_Lot cl INNER JOIN contract c ON cl.contract_id = c.id INNER JOIN zs_project zsp ON c.zs_project_id = zsp.id ");
        sql.append(" WHERE cl.house_status =2 AND c.choose_house_status =2 AND cl.az_project_id =:azProjectId ");
        sql.append(" GROUP BY zsp.id ");

        Query query = this.entityManager.createNativeQuery(sql.toString()).setParameter("azProjectId", azProjectId);
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map> carLotNumMap = Maps.newHashMap();
        if (CollectionUtils.isNotEmpty(mapList)) {
            for (Map map : mapList) {
                String zsProjectId = map.get("zsProjectId").toString();
                carLotNumMap.put(zsProjectId, map);
            }
        }
        return carLotNumMap;
    }


    /**
     * 选房进度一览表【安置】
     * 根据安置项目得到该安置区选房号下各个状态数量情况
     *
     * @return
     */
    public Map<String, Map> getContractProgressStatisticsByAzProjectId() {

        StringBuffer sql = new StringBuffer("SELECT n.azProjectId AS azProjectId,COUNT(c.id) as contractTotal, ");
        sql.append(" SUM(CASE WHEN c.choose_house_status =2 THEN 1 ELSE 0 END) AS chooseHouseStatusCount, ");
        sql.append(" SUM(CASE WHEN c.finance_Check_Status =2 THEN 1 ELSE 0 END) AS financeCheckStatusCount, ");
        sql.append(" SUM(CASE WHEN c.az_Sign_Status =2 THEN 1 ELSE 0 END) AS azSignStatusCount, ");
        sql.append(" SUM(CASE WHEN c.finance_Settle_Status =1 THEN 1 ELSE 0 END) AS financeSettleStatus1Count, ");
        sql.append(" SUM(CASE WHEN c.finance_Settle_Status =2 THEN 1 ELSE 0 END) AS financeSettleStatus2Count, ");
        sql.append(" SUM(CASE WHEN c.finance_Settle_Status =3 THEN 1 ELSE 0 END) AS financeSettleStatus3Count, ");
        sql.append(" SUM(CASE WHEN c.finance_Settle_Status =2 THEN c.final_money ELSE 0 END) AS financeSettleStatus2Money, ");
        sql.append(" SUM(CASE WHEN c.take_House_Status =2 THEN 1 ELSE 0 END) AS takeHouseStatusCount, ");
        sql.append(" SUM(CASE WHEN c.daff_Status =2 THEN 1 ELSE 0 END) AS daffStatusCount, ");
        sql.append(" SUM(CASE WHEN c.bz_Status =2 THEN 1 ELSE 0 END) AS bzStatusCount ");
        sql.append(" FROM contract c,");
        sql.append(" (SELECT DISTINCT c.id AS contractId,h.az_project_id AS azProjectId FROM house h,contract c WHERE h.contract_id = c.id) n  ");
        sql.append(" WHERE c.id = n.contractId ");
        sql.append(" GROUP BY n.azProjectId ");

        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map> contractNumMap = Maps.newHashMap();
        if (CollectionUtils.isNotEmpty(mapList)) {
            for (Map map : mapList) {
                String azProjectId = map.get("azProjectId").toString();
                contractNumMap.put(azProjectId, map);
            }
        }
        return contractNumMap;
    }

    /**
     * 选房进度一览表【安置】
     * 根据安置项目得到该安置区下各个房源状态数量情况
     *
     * @return
     */
    public Map<String, Map> getHouseProgressStatisticsByAzProjectId() {

        StringBuffer sql = new StringBuffer("SELECT azp.id AS azProjectId,COUNT(h.id) as houseTotal, ");
        sql.append(" SUM(CASE WHEN h.house_status = 2 THEN 1 ELSE 0 END) AS chooseHouseStatusCount, ");
        sql.append(" SUM(CASE WHEN h.finance_Settle_Status = 1 THEN 1 ELSE 0 END) AS financeSettleStatus1Count, ");
        sql.append(" SUM(CASE WHEN h.finance_Settle_Status = 2 THEN 1 ELSE 0 END) AS financeSettleStatus2Count, ");
        sql.append(" SUM(CASE WHEN h.take_House_Status = 2 THEN 1 ELSE 0 END) AS takeHouseStatusCount, ");
        sql.append(" SUM(CASE WHEN h.bz_Status = 2 THEN 1 ELSE 0 END) AS bzStatusCount ");
        sql.append(" FROM House h INNER JOIN  az_Project azp ON h.az_project_id=azp.id ");
        sql.append(" WHERE h.house_status = 2 ");
        sql.append(" GROUP BY azp.id");

        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map> contractNumMap = Maps.newHashMap();
        if (CollectionUtils.isNotEmpty(mapList)) {
            for (Map map : mapList) {
                String azProjectId = map.get("azProjectId").toString();
                contractNumMap.put(azProjectId, map);
            }
        }
        return contractNumMap;
    }

    /**
     * 选房进度一览表【安置】
     * 根据安置项目得到该安置区下各个房源状态数量情况
     *
     * @return
     */
    public Map<String, Map> getCarLotProgressStatisticsByAzProjectId() {

        StringBuffer sql = new StringBuffer("SELECT azp.id AS azProjectId,COUNT(cl.id) as carLotTotal, ");
        sql.append(" SUM(CASE WHEN cl.house_status = 2 THEN 1 ELSE 0 END) AS chooseCarLotStatusCount, ");
        sql.append(" SUM(CASE WHEN cl.take_House_Status = 2 THEN 1 ELSE 0 END) AS takeCarLotStatusCount, ");
        sql.append(" SUM(CASE WHEN cl.bz_Status = 2 THEN 1 ELSE 0 END) AS bzCarLotStatusCount ");
        sql.append(" FROM Car_Lot cl INNER JOIN  az_Project azp ON cl.az_project_id=azp.id ");
        sql.append(" WHERE cl.house_status = 2 ");
        sql.append(" GROUP BY azp.id");

        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map> contractNumMap = Maps.newHashMap();
        if (CollectionUtils.isNotEmpty(mapList)) {
            for (Map map : mapList) {
                String azProjectId = map.get("azProjectId").toString();
                contractNumMap.put(azProjectId, map);
            }
        }
        return contractNumMap;
    }


    /**
     * 选房进度一览表【安置】
     * 根据安置项目得到该安置区下各个房源状态数量情况
     *
     * @return
     */
    public Map<String, Map> getStorageRoomProgressStatisticsByAzProjectId() {

        StringBuffer sql = new StringBuffer("SELECT azp.id AS azProjectId,COUNT(sr.id) as carLotTotal, ");
        sql.append(" SUM(CASE WHEN sr.house_status = 2 THEN 1 ELSE 0 END) AS chooseStorageRoomStatusCount, ");
        sql.append(" SUM(CASE WHEN sr.take_House_Status = 2 THEN 1 ELSE 0 END) AS takeStorageRoomStatusCount, ");
        sql.append(" SUM(CASE WHEN sr.bz_Status = 2 THEN 1 ELSE 0 END) AS bzStorageRoomStatusCount ");
        sql.append(" FROM Storage_Room sr INNER JOIN  az_Project azp ON sr.az_project_id=azp.id ");
        sql.append(" WHERE sr.house_status = 2 ");
        sql.append(" GROUP BY azp.id");

        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map> contractNumMap = Maps.newHashMap();
        if (CollectionUtils.isNotEmpty(mapList)) {
            for (Map map : mapList) {
                String azProjectId = map.get("azProjectId").toString();
                contractNumMap.put(azProjectId, map);
            }
        }
        return contractNumMap;
    }

    /**
     * 选房进度一览表【征收】
     * 根据安置项目得到在该安置区下各个征收项目的状态数量情况
     *
     * @return
     */
    public Map<String, Map> getContractProgressZsStatisticsByAzProjectId(Integer azProjectId) {

        StringBuffer sql = new StringBuffer("SELECT c.zs_project_id AS zsProjectId,COUNT(c.id) as contractTotal, ");
        sql.append(" SUM(CASE WHEN c.choose_house_status =2 THEN 1 ELSE 0 END) AS chooseHouseStatusCount, ");
        sql.append(" SUM(CASE WHEN c.finance_Check_Status =2 THEN 1 ELSE 0 END) AS financeCheckStatusCount, ");
        sql.append(" SUM(CASE WHEN c.az_Sign_Status =2 THEN 1 ELSE 0 END) AS azSignStatusCount, ");
        sql.append(" SUM(CASE WHEN c.finance_Settle_Status =1 THEN 1 ELSE 0 END) AS financeSettleStatus1Count, ");
        sql.append(" SUM(CASE WHEN c.finance_Settle_Status =2 THEN 1 ELSE 0 END) AS financeSettleStatus2Count, ");
        sql.append(" SUM(CASE WHEN c.finance_Settle_Status =2 THEN c.final_money ELSE 0 END) AS financeSettleStatus2Money, ");
        sql.append(" SUM(CASE WHEN c.finance_Settle_Status =3 THEN 1 ELSE 0 END) AS financeSettleStatus3Count, ");
        sql.append(" SUM(CASE WHEN c.take_House_Status =2 THEN 1 ELSE 0 END) AS takeHouseStatusCount, ");
        sql.append(" SUM(CASE WHEN c.daff_Status =2 THEN 1 ELSE 0 END) AS daffStatusCount, ");
        sql.append(" SUM(CASE WHEN c.bz_Status =2 THEN 1 ELSE 0 END) AS bzStatusCount ");
        sql.append(" FROM contract c inner join zs_project zsp on c.zs_project_id= zsp.id ,");
        sql.append(" (SELECT DISTINCT c.id AS contractId,h.az_project_id AS azProjectId FROM house h,contract c WHERE h.contract_id = c.id) n  ");
        sql.append(" WHERE c.id = n.contractId AND n.azProjectId=:azProjectId ");
        sql.append(" GROUP BY c.zs_project_id ");

        Query query = this.entityManager.createNativeQuery(sql.toString()).setParameter("azProjectId", azProjectId);
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map> contractNumMap = Maps.newHashMap();
        if (CollectionUtils.isNotEmpty(mapList)) {
            for (Map map : mapList) {
                String zsProjectId = map.get("zsProjectId").toString();
                contractNumMap.put(zsProjectId, map);
            }
        }
        return contractNumMap;
    }

    /**
     * 选房进度一览表【征收】
     * 根据安置项目得到该安置区下各个房源状态数量情况
     *
     * @return
     */
    public Map<String, Map> getHouseProgressZsStatisticsByAzProjectId(Integer azProjectId) {

        StringBuffer sql = new StringBuffer("SELECT zsp.id AS zsProjectId,COUNT(h.id) AS houseTotal, ");
        sql.append(" SUM(CASE WHEN h.house_status = 2 THEN 1 ELSE 0 END) AS chooseHouseStatusCount, ");
        sql.append(" SUM(CASE WHEN h.finance_Settle_Status = 1 THEN 1 ELSE 0 END) AS financeSettleStatus1Count, ");
        sql.append(" SUM(CASE WHEN h.finance_Settle_Status = 2 THEN 1 ELSE 0 END) AS financeSettleStatus2Count, ");
        sql.append(" SUM(CASE WHEN h.take_House_Status = 2 THEN 1 ELSE 0 END) AS takeHouseStatusCount, ");
        sql.append(" SUM(CASE WHEN h.bz_Status = 2 THEN 1 ELSE 0 END) AS bzStatusCount ");
        sql.append(" FROM House h INNER JOIN  az_Project azp ON h.az_project_id=azp.id ");
        sql.append(" INNER JOIN contract c ON h.contract_id=c.id INNER JOIN zs_project zsp ON c.zs_project_id=zsp.id ");
        sql.append(" WHERE h.house_status = 2 AND azp.id=:azProjectId ");
        sql.append(" GROUP BY zsp.id");

        Query query = this.entityManager.createNativeQuery(sql.toString()).setParameter("azProjectId", azProjectId);
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map> contractNumMap = Maps.newHashMap();
        if (CollectionUtils.isNotEmpty(mapList)) {
            for (Map map : mapList) {
                String zsProjectId = map.get("zsProjectId").toString();
                contractNumMap.put(zsProjectId, map);
            }
        }
        return contractNumMap;
    }

    /**
     * 选房进度一览表【征收】
     * 根据安置项目得到该安置区下各个房源状态数量情况
     *
     * @return
     */
    public Map<String, Map> getCarLotProgressZsStatisticsByAzProjectId(Integer azProjectId) {

        StringBuffer sql = new StringBuffer("SELECT zsp.id AS zsProjectId,COUNT(cl.id) AS carLotTotal, ");
        sql.append(" SUM(CASE WHEN cl.house_status = 2 THEN 1 ELSE 0 END) AS chooseCarLotStatusCount, ");
        sql.append(" SUM(CASE WHEN cl.take_House_Status = 2 THEN 1 ELSE 0 END) AS takeCarLotStatusCount, ");
        sql.append(" SUM(CASE WHEN cl.bz_Status = 2 THEN 1 ELSE 0 END) AS bzCarLotStatusCount ");
        sql.append(" FROM Car_Lot cl INNER JOIN  az_Project azp ON cl.az_project_id=azp.id ");
        sql.append(" INNER JOIN contract c ON cl.contract_id=c.id INNER JOIN zs_project zsp ON c.zs_project_id=zsp.id ");
        sql.append(" WHERE cl.house_status = 2 AND azp.id=:azProjectId ");
        sql.append(" GROUP BY zsp.id");

        Query query = this.entityManager.createNativeQuery(sql.toString()).setParameter("azProjectId", azProjectId);
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map> contractNumMap = Maps.newHashMap();
        if (CollectionUtils.isNotEmpty(mapList)) {
            for (Map map : mapList) {
                String zsProjectId = map.get("zsProjectId").toString();
                contractNumMap.put(zsProjectId, map);
            }
        }
        return contractNumMap;
    }

    /**
     * 选房进度一览表【征收】
     * 根据安置项目得到该安置区下各个房源状态数量情况
     *
     * @return
     */
    public Map<String, Map> getStorageRoomProgressZsStatisticsByAzProjectId(Integer azProjectId) {

        StringBuffer sql = new StringBuffer("SELECT zsp.id AS zsProjectId,COUNT(sr.id) AS storageRoomTotal, ");
        sql.append(" SUM(CASE WHEN sr.house_status = 2 THEN 1 ELSE 0 END) AS chooseStorageRoomStatusCount, ");
        sql.append(" SUM(CASE WHEN sr.take_House_Status = 2 THEN 1 ELSE 0 END) AS takeStorageRoomStatusCount, ");
        sql.append(" SUM(CASE WHEN sr.bz_Status = 2 THEN 1 ELSE 0 END) AS bzStorageRoomStatusCount ");
        sql.append(" FROM Storage_Room sr INNER JOIN  az_Project azp ON sr.az_project_id=azp.id ");
        sql.append(" INNER JOIN contract c ON sr.contract_id=c.id INNER JOIN zs_project zsp ON c.zs_project_id=zsp.id ");
        sql.append(" WHERE sr.house_status = 2 AND azp.id=:azProjectId ");
        sql.append(" GROUP BY zsp.id");

        Query query = this.entityManager.createNativeQuery(sql.toString()).setParameter("azProjectId", azProjectId);
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map> contractNumMap = Maps.newHashMap();
        if (CollectionUtils.isNotEmpty(mapList)) {
            for (Map map : mapList) {
                String zsProjectId = map.get("zsProjectId").toString();
                contractNumMap.put(zsProjectId, map);
            }
        }
        return contractNumMap;
    }


    /**
     * 得到所有安置项目被选的征收项目数量
     *
     * @return
     */
    public Map<String, Integer> getZsProjectCountByAzProject() {
        Map<String, Integer> resultMap = Maps.newHashMap();
        List<AzProject> azProjectList = this.azProjectService.azProjectList();
        if (CollectionUtils.isNotEmpty(azProjectList)) {
            for (AzProject azProject : azProjectList) {
                String zsProjectIdStr = this.findZsProjectIdSetByAzProjectId(azProject.getId());
                if (!zsProjectIdStr.isEmpty() & zsProjectIdStr.length() > 0) {
                    List<ZsProject> zsProjectList = this.zsProjectService.getZsProjectListByZsProjectIdStr(zsProjectIdStr);
                    resultMap.put(azProject.getId().toString(), zsProjectList.size());
                }
            }
        }
        return resultMap;
    }

    /**
     * 得到houseList
     *
     * @param azProjectId
     * @param buildNum
     * @return
     */
    public List<House> getStatisticsSelectedContractList(HttpServletRequest request) {
        Integer azProjectId = ParamUtils.getInt(request, "azProjectId", 0);
        Integer zsProjectId = ParamUtils.getInt(request, "zsProjectId", 0);
        String yearStr = ParamUtils.getString(request, "yearStr", "");
        String townCode = ParamUtils.getString(request, "townCode", "");
        StringBuffer sql = new StringBuffer("  from House h inner join fetch h.contract c inner join fetch c.zsProject zsp inner join fetch h.azProject azp where 1=1");
        Map<String, Object> map = Maps.newLinkedHashMap();
        if (azProjectId != null && azProjectId != 0) {
            sql.append(" and azp.id = :azProjectId");
            map.put("azProjectId", azProjectId);
        }
        if (zsProjectId != null && zsProjectId != 0) {
            sql.append(" and zsp.id = :zsProjectId");
            map.put("zsProjectId", zsProjectId);
        }
        if (StringUtils.isNotEmpty(yearStr)) {
            List<Integer> houseIdList = new ArrayList<>();
            houseIdList.add(0);//防止为空
            houseIdList = this.houseService.getHouseIdListByChooseHouseDateYear(yearStr);
            sql.append(" and h.id in :houseIdList");
            map.put("houseIdList", houseIdList);

        }
        if (StringUtils.isNotEmpty(townCode)) {
            sql.append(" and azp.town= :townCode ");
            map.put("townCode", townCode);
        }
        sql.append(" group by azp.id,zsp.id,c.id order by zsp.id,c.chooseHouseNum ");
        return this.houseService.findHql(sql.toString(), map);
    }

    /**
     * 统计已选房源明细，不分页
     *
     * @return
     */
    public PageBean<House> getStatisticsSelectedHousePageBean(HttpServletRequest request) {
        Integer azProjectId = ParamUtils.getInt(request, "azProjectId", 0);
        Integer zsProjectId = ParamUtils.getInt(request, "zsProjectId", 0);
        String houseHold = ParamUtils.getString(request, "houseHold", "");
        String houseType = ParamUtils.getString(request, "houseType", "");
        String yearStr = ParamUtils.getString(request, "yearStr", "");
        String townCode = ParamUtils.getString(request, "townCode", "");
        //查询语句
        StringBuffer query = new StringBuffer("h from House h inner join fetch h.contract c inner join fetch c.zsProject zsp  inner join fetch h.azProject azp");

        //默认排序(PageBean指定排序字段类型的话，则默认排序不使用)
        StringBuffer orderBy = new StringBuffer("zsp.id,c.id,h.id");
        PageBean pageBean = new PageBean();
        pageBean.setPageSize(Integer.MAX_VALUE);
        Wrapper wrapper = new Wrapper(pageBean, query, null, orderBy);
        wrapper.andEq("h.houseStatus", Status.YES.getIndex());
        wrapper.andEq("c.chooseHouseStatus", Status.YES.getIndex());

        if (azProjectId != null && azProjectId != 0) {
            wrapper.andEq("azp.id", azProjectId);
        }
        if (zsProjectId != null && zsProjectId != 0) {
            wrapper.andEq("zsp.id", zsProjectId);
        }
        if (StringUtils.isNotEmpty(houseHold)) {
            wrapper.andEq("h.houseHold", houseHold);
        }
        if (StringUtils.isNotEmpty(houseType)) {
            wrapper.andEq("h.houseType", houseType);
        }
        if (StringUtils.isNotEmpty(yearStr)) {
            List<Integer> houseIdList = new ArrayList<>();
            houseIdList.add(0);//防止为空
            houseIdList = this.houseService.getHouseIdListByChooseHouseDateYear(yearStr);
            wrapper.andIn("h.id", houseIdList);
        }
        if (StringUtils.isNotEmpty(townCode)) {
            wrapper.andEq("azp.town", townCode);
        }

        wrapper.initSearchParams();
        return this.houseService.pageHql(wrapper);
    }


    /**
     * 统计已选车位明细，不分页
     *
     * @return
     */
    public PageBean<CarLot> getStatisticsSelectedCarLotPageBean(HttpServletRequest request) {
        Integer azProjectId = ParamUtils.getInt(request, "azProjectId", 0);
        Integer zsProjectId = ParamUtils.getInt(request, "zsProjectId", 0);
        String carLotType = ParamUtils.getString(request, "carLotType", "");
        Integer financeSettleStatus = ParamUtils.getInt(request, "financeSettleStatus", 0);
        String yearStr = ParamUtils.getString(request, "yearStr", "");
        String townCode = ParamUtils.getString(request, "townCode", "");
        //查询语句
        StringBuffer query = new StringBuffer("cl from CarLot cl inner join fetch cl.contract c inner join fetch c.zsProject zsp  inner join fetch cl.azProject azp");

        //默认排序(PageBean指定排序字段类型的话，则默认排序不使用)
        StringBuffer orderBy = new StringBuffer("azp.id,zsp.id,c.id,cl.id");
        PageBean pageBean = new PageBean();
        pageBean.setPageSize(Integer.MAX_VALUE);
        Wrapper wrapper = new Wrapper(pageBean, query, null, orderBy);
        wrapper.andEq("cl.houseStatus", Status.YES.getIndex());
        wrapper.andEq("c.chooseHouseStatus", Status.YES.getIndex());

        if (azProjectId != null && azProjectId != 0) {
            wrapper.andEq("azp.id", azProjectId);
        }
        if (zsProjectId != null && zsProjectId != 0) {
            wrapper.andEq("zsp.id", zsProjectId);
        }
        if (financeSettleStatus != null && financeSettleStatus != 0) {
            wrapper.andEq("cl.financeSettleStatus", financeSettleStatus);
        }
        if (carLotType != null && carLotType != "") {
            String str[] = carLotType.split(",");
            Integer[] ints = (Integer[]) ConvertUtils.convert(str, Integer.class);
            List<Integer> typeList = Arrays.asList(ints);
            wrapper.andIn("cl.carLotType", typeList);
        }
        if (StringUtils.isNotEmpty(yearStr)) {
            List<Integer> carLotIdList = new ArrayList<>();
            carLotIdList.add(0);//防止为空
            carLotIdList = this.carLotService.getCarLotIdListByChooseHouseDateYear(yearStr);
            wrapper.andIn("cl.id", carLotIdList);
        }
        if (StringUtils.isNotEmpty(townCode)) {
            wrapper.andEq("azp.town", townCode);
        }


        wrapper.initSearchParams();
        return this.carLotService.pageHql(wrapper);
    }


    /**
     * 统计已选储物间明细，不分页
     *
     * @return
     */
    public PageBean<StorageRoom> getStatisticsSelectedStorageRoomPageBean(HttpServletRequest request) {
        Integer azProjectId = ParamUtils.getInt(request, "azProjectId", 0);
        Integer zsProjectId = ParamUtils.getInt(request, "zsProjectId", 0);
        String yearStr = ParamUtils.getString(request, "yearStr", "");
        String townCode = ParamUtils.getString(request, "townCode", "");
        //查询语句
        StringBuffer query = new StringBuffer("sr from StorageRoom sr inner join fetch sr.contract c inner join fetch c.zsProject zsp  inner join fetch sr.azProject azp");

        //默认排序(PageBean指定排序字段类型的话，则默认排序不使用)
        StringBuffer orderBy = new StringBuffer("azp.id,zsp.id,c.id,sr.id");
        PageBean pageBean = new PageBean();
        pageBean.setPageSize(Integer.MAX_VALUE);
        Wrapper wrapper = new Wrapper(pageBean, query, null, orderBy);
        wrapper.andEq("sr.houseStatus", Status.YES.getIndex());
        wrapper.andEq("c.chooseHouseStatus", Status.YES.getIndex());

        if (azProjectId != null && azProjectId != 0) {
            wrapper.andEq("azp.id", azProjectId);
        }
        if (zsProjectId != null && zsProjectId != 0) {
            wrapper.andEq("zsp.id", zsProjectId);
        }
        if (StringUtils.isNotEmpty(yearStr)) {
            List<Integer> storageRoomList = new ArrayList<>();
            storageRoomList.add(0);//防止为空
            storageRoomList = this.storageRoomService.getStorageRoomIdListByChooseHouseDateYear(yearStr);
            wrapper.andIn("sr.id", storageRoomList);
        }
        if (StringUtils.isNotEmpty(townCode)) {
            wrapper.andEq("azp.town", townCode);
        }
        wrapper.initSearchParams();
        return this.storageRoomService.pageHql(wrapper);
    }


    /**
     * 统计已选店铺明细，不分页
     *
     * @return
     */
    public PageBean<ShopHouse> getStatisticsSelectedShopHousePageBean(HttpServletRequest request) {
        Integer azProjectId = ParamUtils.getInt(request, "azProjectId", 0);
        Integer zsProjectId = ParamUtils.getInt(request, "zsProjectId", 0);
        String yearStr = ParamUtils.getString(request, "yearStr", "");
        String townCode = ParamUtils.getString(request, "townCode", "");
        //查询语句
        StringBuffer query = new StringBuffer("sh from ShopHouse sh inner join fetch sh.contract c inner join fetch c.zsProject zsp  inner join fetch sh.azProject azp");

        //默认排序(PageBean指定排序字段类型的话，则默认排序不使用)
        StringBuffer orderBy = new StringBuffer("azp.id,zsp.id,c.id,sh.id");
        PageBean pageBean = new PageBean();
        pageBean.setPageSize(Integer.MAX_VALUE);
        Wrapper wrapper = new Wrapper(pageBean, query, null, orderBy);
        wrapper.andEq("sh.houseStatus", Status.YES.getIndex());
        wrapper.andEq("c.chooseHouseStatus", Status.YES.getIndex());

        if (azProjectId != null && azProjectId != 0) {
            wrapper.andEq("azp.id", azProjectId);
        }
        if (zsProjectId != null && zsProjectId != 0) {
            wrapper.andEq("zsp.id", zsProjectId);
        }
        if (StringUtils.isNotEmpty(yearStr)) {
            List<Integer> shopHouseIdList = new ArrayList<>();
            shopHouseIdList.add(0);//防止为空
            shopHouseIdList = this.shopHouseService.getShopHouseIdListByChooseHouseDateYear(yearStr);
            wrapper.andIn("sh.id", shopHouseIdList);
        }
        if (StringUtils.isNotEmpty(townCode)) {
            wrapper.andEq("azp.town", townCode);
        }
        wrapper.initSearchParams();
        return this.shopHouseService.pageHql(wrapper);
    }


    /**
     * 统计剩余房源明细，不分页
     *
     * @return
     */
    public PageBean<House> getStatisticsHousePageBean(HttpServletRequest request) {
        Integer azProjectId = ParamUtils.getInt(request, "azProjectId", 0);
        String houseHold = ParamUtils.getString(request, "houseHold", "");
        String houseType = ParamUtils.getString(request, "houseType", "");
        String houseStatus = ParamUtils.getString(request, "houseStatus", "");
        String town = ParamUtils.getString(request, "town", "");

        //查询语句
        StringBuffer query = new StringBuffer("h from House h  inner join fetch h.azProject azp");

        //默认排序(PageBean指定排序字段类型的话，则默认排序不使用)
        StringBuffer orderBy = new StringBuffer("azp.id,CONVERT(SUBSTRING_INDEX(h.buildNum,'-',1),SIGNED),CONVERT(SUBSTRING_INDEX(h.buildNum,'-',-1),SIGNED),h.floor,h.houseNum");
        PageBean pageBean = new PageBean();
        pageBean.setPageSize(Integer.MAX_VALUE);
        Wrapper wrapper = new Wrapper(pageBean, query, null, orderBy);
        if (StringUtils.isNotEmpty(houseStatus) && !houseStatus.equals("2")) {
            if (houseStatus.equals("1")) {
                wrapper.andEq("h.houseStatus", Status.YES.getIndex());
            } else if (houseStatus.equals("3")) {
                wrapper.andEq("h.houseStatus", Status.TUI.getIndex());
            }
        } else {
            wrapper.andEq("h.houseStatus", Status.NO.getIndex());
        }
        if (azProjectId != null && azProjectId != 0) {
            wrapper.andEq("azp.id", azProjectId);
        }
        if (StringUtils.isNotEmpty(houseHold)) {
            wrapper.andEq("h.houseHold", houseHold);
        }
        if (StringUtils.isNotEmpty(houseType)) {
            wrapper.andEq("h.houseType", houseType);
        }
        if (StringUtils.isNotEmpty(town)) {
            wrapper.andEq("azp.town", town);
        }

        wrapper.initSearchParams();
        return this.houseService.pageHql(wrapper);
    }

    /**
     * 统计剩余店铺明细，不分页
     *
     * @return
     */
    public PageBean<ShopHouse> getStatisticsShopHousePageBean(HttpServletRequest request) {
        Integer azProjectId = ParamUtils.getInt(request, "azProjectId", 0);
        String town = ParamUtils.getString(request, "town", "");

        //查询语句
        StringBuffer query = new StringBuffer("sh from ShopHouse sh  inner join fetch sh.azProject azp");

        //默认排序(PageBean指定排序字段类型的话，则默认排序不使用)
        StringBuffer orderBy = new StringBuffer("azp.id,sh.buildNum,sh.floor,sh.houseNum");
        PageBean pageBean = new PageBean();
        pageBean.setPageSize(Integer.MAX_VALUE);
        Wrapper wrapper = new Wrapper(pageBean, query, null, orderBy);
        wrapper.andEq("sh.houseStatus", Status.NO.getIndex());

        if (azProjectId != null && azProjectId != 0) {
            wrapper.andEq("azp.id", azProjectId);
        }
        if (StringUtils.isNotEmpty(town)) {
            wrapper.andEq("azp.town", town);
        }
        wrapper.initSearchParams();
        return this.shopHouseService.pageHql(wrapper);
    }


    /**
     * 统计剩余车位明细，不分页
     *
     * @return
     */
    public PageBean<CarLot> getStatisticsCarLotPageBean(HttpServletRequest request) {
        Integer azProjectId = ParamUtils.getInt(request, "azProjectId", 0);
        String carLotType = ParamUtils.getString(request, "carLotType", "");
        String town = ParamUtils.getString(request, "town", "");

        //查询语句
        StringBuffer query = new StringBuffer("cl from CarLot cl  inner join fetch cl.azProject azp");

        //默认排序(PageBean指定排序字段类型的话，则默认排序不使用)
        StringBuffer orderBy = new StringBuffer("azp.id,cl.buildNum,cl.floor,cl.houseNum");
        PageBean pageBean = new PageBean();
        pageBean.setPageSize(Integer.MAX_VALUE);
        Wrapper wrapper = new Wrapper(pageBean, query, null, orderBy);
        wrapper.andEq("cl.houseStatus", Status.NO.getIndex());

        if (azProjectId != null && azProjectId != 0) {
            wrapper.andEq("azp.id", azProjectId);
        }
        if (StringUtils.isNotEmpty(town)) {
            wrapper.andEq("azp.town", town);
        }
        if (carLotType != null && carLotType != "") {
            String str[] = carLotType.split(",");
            Integer[] ints = (Integer[]) ConvertUtils.convert(str, Integer.class);
            List<Integer> typeList = Arrays.asList(ints);
            wrapper.andIn("cl.carLotType", typeList);
        }
        wrapper.initSearchParams();
        return this.carLotService.pageHql(wrapper);
    }


    /**
     * 统计剩余车位明细，不分页
     *
     * @return
     */
    public PageBean<StorageRoom> getStatisticsStorageRoomPageBean(HttpServletRequest request) {
        Integer azProjectId = ParamUtils.getInt(request, "azProjectId", 0);
        String town = ParamUtils.getString(request, "town", "");
        //查询语句
        StringBuffer query = new StringBuffer("sr from StorageRoom sr  inner join fetch sr.azProject azp");

        //默认排序(PageBean指定排序字段类型的话，则默认排序不使用)
        StringBuffer orderBy = new StringBuffer("azp.id,sr.buildNum,sr.floor,sr.houseNum");
        PageBean pageBean = new PageBean();
        pageBean.setPageSize(Integer.MAX_VALUE);
        Wrapper wrapper = new Wrapper(pageBean, query, null, orderBy);
        wrapper.andEq("sr.houseStatus", Status.NO.getIndex());

        if (azProjectId != null && azProjectId != 0) {
            wrapper.andEq("azp.id", azProjectId);
        }
        if (StringUtils.isNotEmpty(town)) {
            wrapper.andEq("azp.town", town);
        }
        wrapper.initSearchParams();
        return this.storageRoomService.pageHql(wrapper);
    }

    /**
     * 房源情况统计表
     * 根据安置项目得到该安置区房源情况
     *
     * @return
     */
    public Map<String, Map> getHouseByAzProjectId() {

        StringBuffer sql = new StringBuffer("SELECT azp.id AS azProjectId,azp.project_name AS azProjectName,");

        sql.append(" SUM(CASE WHEN h.house_status=1 AND h.house_hold = '0100' THEN 1 ELSE 0 END) AS 0100HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_status=1 AND h.house_hold = '0100' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0100HouseArea, ");

        sql.append(" SUM(CASE WHEN h.house_status=1 AND h.house_hold = '0102' THEN 1 ELSE 0 END) AS 0102HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_status=1 AND h.house_hold = '0102' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0102HouseArea, ");
        sql.append(" SUM(CASE WHEN h.house_status=1 AND h.house_hold = '0201' THEN 1 ELSE 0 END) AS 0201HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_status=1 AND h.house_hold = '0201' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0201HouseArea, ");
        sql.append(" SUM(CASE WHEN h.house_status=1 AND h.house_hold = '0202' THEN 1 ELSE 0 END) AS 0202HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_status=1 AND h.house_hold = '0202' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0202HouseArea, ");
        sql.append(" SUM(CASE WHEN h.house_status=1 AND h.house_hold = '0302' THEN 1 ELSE 0 END) AS 0302HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_status=1 AND h.house_hold = '0302' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0302HouseArea, ");
        sql.append(" SUM(CASE WHEN h.house_status=1 AND h.house_hold = '0402' THEN 1 ELSE 0 END) AS 0402HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_status=1 AND h.house_hold = '0402' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0402HouseArea, ");
        sql.append(" SUM(CASE WHEN h.house_status=1 AND h.house_hold = '0502' THEN 1 ELSE 0 END) AS 0502HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_status=1 AND h.house_hold = '0502' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0502HouseArea, ");

        sql.append(" SUM(CASE WHEN h.house_status=1 AND h.house_hold = '1000' THEN 1 ELSE 0 END) AS fsHouseNum,");
        sql.append(" SUM(CASE WHEN h.house_status=1 AND h.house_hold = '1000' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS fsHouseArea, ");

        sql.append(" SUM(CASE WHEN h.house_status=1 THEN 1 ELSE 0 END) AS houseNum,");
        sql.append(" SUM(CASE WHEN h.house_status=1 THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS houseArea, ");

        sql.append(" SUM(CASE WHEN h.house_status=3 THEN 1 ELSE 0 END) AS houseYlNum,");
        sql.append(" SUM(CASE WHEN h.house_status=3 THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS houseYlArea ");
        sql.append(" FROM house h INNER JOIN  az_project azp ON h.az_project_id = azp.id ");
        sql.append(" GROUP BY azp.id ORDER BY azp.id");

        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map> houseNumMap = Maps.newHashMap();
        if (CollectionUtils.isNotEmpty(mapList)) {
            for (Map map : mapList) {
                String azProjectId = map.get("azProjectId").toString();
                houseNumMap.put(azProjectId, map);
            }
        }
        return houseNumMap;
    }


    /**
     * 选房情况汇总表
     * 根据安置项目得到已选该安置区房源的数量及面积
     *
     * @return
     */
    public Map<String, Map> getHouseStatisticsByContractIdAndHouseHold() {

        StringBuffer sql = new StringBuffer("SELECT c.id AS contractId,");
        sql.append(" SUM(CASE WHEN h.house_hold = '0100' THEN 1 ELSE 0 END) AS 0100HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_hold = '0100' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0100HouseArea, ");
        sql.append(" SUM(CASE WHEN h.house_hold = '0102' THEN 1 ELSE 0 END) AS 0102HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_hold = '0102' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0102HouseArea, ");
        sql.append(" SUM(CASE WHEN h.house_hold = '0202' THEN 1 ELSE 0 END) AS 0202HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_hold = '0202' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0202HouseArea, ");
        sql.append(" SUM(CASE WHEN h.house_hold = '0302' THEN 1 ELSE 0 END) AS 0302HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_hold = '0302' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0302HouseArea, ");
        sql.append(" SUM(CASE WHEN h.house_hold = '0402' THEN 1 ELSE 0 END) AS 0402HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_hold = '0402' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0402HouseArea, ");
        sql.append(" SUM(CASE WHEN h.house_hold = '1000' THEN 1 ELSE 0 END) AS 1000HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_hold = '1000' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 1000HouseAree  ");

        sql.append(" FROM house h INNER JOIN contract c ON h.contract_id = c.id ");
        sql.append(" WHERE h.house_status =2 ");
        sql.append(" GROUP BY c.id ORDER BY c.id");

        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map> houseNumMap = Maps.newHashMap();
        if (CollectionUtils.isNotEmpty(mapList)) {
            for (Map map : mapList) {
                String contractId = map.get("contractId").toString();
                houseNumMap.put(contractId, map);
            }
        }
        return houseNumMap;
    }

    /**
     * 项目选房情况汇总表
     * 根据征收项目得到安置项目各户型选房情况
     *
     * @return
     */
    public Map<String, Map<String,Map>> getHouseZsStatisticsByZsProjectIdAndAzProjectId() {

        StringBuffer sql = new StringBuffer("SELECT zsp.id AS zsProjectId,zsp.project_name AS zsProjectName,");
        sql.append(" zsp.start_year AS zsProjectDate,");
        sql.append(" azp.id AS azProjectId,");

        sql.append(" SUM(CASE WHEN h.house_hold = '0100' THEN 1 ELSE 0 END) AS 0100HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_hold = '0100' AND h.house_type = '01' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0100HouseArea, ");

        sql.append(" SUM(CASE WHEN h.house_hold = '0102' THEN 1 ELSE 0 END) AS 0102HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_hold = '0102' AND h.house_type = '01' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0102HouseArea, ");

        sql.append(" SUM(CASE WHEN h.house_hold = '0202' AND h.house_type = '01' THEN 1 ELSE 0 END) AS 0202HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_hold = '0202' AND h.house_type = '01' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0202HouseArea, ");

        sql.append(" SUM(CASE WHEN h.house_hold = '0302' AND h.house_type = '01' THEN 1 ELSE 0 END) AS 0302HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_hold = '0302' AND h.house_type = '01' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0302HouseArea, ");

        sql.append(" SUM(CASE WHEN h.house_hold = '0402' AND h.house_type = '01' THEN 1 ELSE 0 END) AS 0402HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_hold = '0402' AND h.house_type = '01' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0402HouseArea, ");

        sql.append(" SUM(CASE WHEN h.house_hold = '0502' AND h.house_type = '01' THEN 1 ELSE 0 END) AS 0502HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_hold = '0502' AND h.house_type = '01' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 0502HouseArea, ");

        sql.append(" SUM(CASE WHEN h.house_hold = '1000' THEN 1 ELSE 0 END) AS 1000HouseNum,");
        sql.append(" SUM(CASE WHEN h.house_hold = '1000' THEN (IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) ELSE 0 END) AS 1000HouseArea, ");
        //总数量、面积
        sql.append(" IFNULL(COUNT(DISTINCT(h.id)),0) AS houseNum,");
        sql.append(" SUM(IFNULL(h.property_Area,0) + IFNULL(h.pool_area,0)) AS houseArea ");
        sql.append(" FROM house h INNER JOIN contract c ON h.contract_id = c.id INNER JOIN zs_project zsp ON c.zs_project_id = zsp.id INNER JOIN az_project azp on h.az_project_id=azp.id");
        sql.append(" WHERE h.house_status =2 AND c.choose_house_status =2  ");
        sql.append(" GROUP BY zsp.id,azp.id ORDER BY zsp.id,azp.id");

        Query query = this.entityManager.createNativeQuery(sql.toString());
        List<Map> mapList = query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        Map<String, Map<String,Map>> zsProjectCountMap = Maps.newHashMap();

        if (CollectionUtils.isNotEmpty(mapList)) {
            Map<String, Map> azProjectCountMap = Maps.newLinkedHashMap();
            for (Map map : mapList) {

                String azProjectId = map.get("azProjectId").toString();
                String zsProjectId = map.get("zsProjectId").toString();
                azProjectCountMap = zsProjectCountMap.get(zsProjectId) == null ? Maps.newLinkedHashMap() : zsProjectCountMap.get(zsProjectId);
                azProjectCountMap.put(azProjectId, map);
                zsProjectCountMap.put(zsProjectId, azProjectCountMap);
            }
        }

        return zsProjectCountMap;
    }


//    /**弃用
//     * 得到东园片区剩余期房面积
//     * 高铁片区未结算剩余面积
//     *
//     * @return
//     */
//    public BigDecimal getSyAreaByAzProjectId() {
//        StringBuffer sql = new StringBuffer(" SELECT SUM(CASE WHEN c.choose_house_status = 2 THEN IFNULL(c.sycqdh_Area+c.syzggm_Area,0)  ELSE 0 END)  AS syArea ");
//        sql.append(" FROM contract c INNER JOIN  zs_project zsp ON c.zs_project_id = zsp.id ");
//        sql.append(" WHERE c.is_Ji_Suan =2 and c.choose_house_status=2 and  zsp.id in (85,86,87,88,89)  ");
//        Map<String, Object> map = new HashMap<>();
//        return BigDecimalUtils.stringToBigDecimal(String.valueOf(this.findSqlObject(sql.toString(), map).get(0)));
//    }
}
